package cn.edu.sdjzu.xg2121.dao;

import cn.edu.sdjzu.xg2121.domain.Department;
import cn.edu.sdjzu.xg2121.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DepartmentDao {
    private static DepartmentDao departmentDao = new DepartmentDao();
    public DepartmentDao(){}
    public static DepartmentDao getInstance(){
        return departmentDao;
    }
    public boolean delete(int id,Connection connection) throws SQLException {
        String delete_sql = "delete from department where id = ?";
        PreparedStatement pstmt_delete = connection.prepareStatement(delete_sql);
        pstmt_delete.setInt(1,id);
        int rowAffected = pstmt_delete.executeUpdate();
        JdbcHelper.close(pstmt_delete,connection);
        return rowAffected > 0;
    }
    public boolean update(Department department) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String sql_update = "UPDATE department SET description = ?, no = ? ,remarks=?,school_id=?"+
                "WHERE id = ?";
        PreparedStatement pstmt_update =connection.prepareStatement(sql_update);
        pstmt_update.setString(1, department.getDescription());
        pstmt_update.setString(2, department.getNo());
        pstmt_update.setString(3, department.getRemarks());
        pstmt_update.setInt(4, department.getSchool().getId());
        pstmt_update.setInt(5, department.getId());
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);
        return rowAffected > 0;
    }
    public Department find(Integer id) throws SQLException {
        Department desiredDepartments = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM department WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet = pstmt_select_id.executeQuery();
        if (resultSet.next()){
            int id2 = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
//            int school_id = resultSet.getInt("school_id");
//            School school = SchoolService.getInstance().find(school_id);
            desiredDepartments = new Department(id2,description,no,remarks);
        }
        JdbcHelper.close(resultSet,pstmt_select_id,connection);
        return desiredDepartments;
    }
    public void add(Department department) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        connection.setAutoCommit(false);
        try {
            DepartmentDao.getInstance().add(department);
            SchoolDao.getInstance().add(department.getSchool());
            System.out.println("添加成功");
            connection.commit();
        }finally {
            connection.setAutoCommit(true);
        }

    }
}
